/*
 * Copyright (c) 2018, apexes.net. All rights reserved.
 *
 *         http://www.apexes.net
 *
 */
package net.apexes.commons.querydsl.info;

import net.apexes.commons.lang.Checks;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * 
 * @author <a href="mailto:hedyn@foxmail.com">HeDYn</a>
 *
 */
public class TableInfo {

    private static final int FK_COLUMN_NAME = 8;

    private static final int FK_TABLE_NAME = 7;

    private static final int FK_SCHEMA_NAME = 6;

    private static final int FK_NAME = 12;

    private static final int FK_PK_COLUMN_NAME = 4;

    private static final int FK_PK_TABLE_NAME = 3;

    private static final int FK_PK_SCHEMA_NAME = 2;

    private static final int PK_COLUMN_NAME = 4;

    private static final int PK_NAME = 6;

    private final String catalog;
    private final String schema;
    private final String tableName;
    private final boolean lowerCase;
    private final PrimaryKeyInfo primaryKey;
    private final List<ColumnInfo> columns;
    private final List<ForeignKeyInfo> importedKeys;
    private final List<ForeignKeyInfo> exportedKeys;
    private final List<IndexInfo> indexs;

    public TableInfo(DatabaseMetaData md, String catalog, String schema, String tableName)
            throws SQLException {
        this(md, catalog, schema, tableName, false);
    }

    public TableInfo(DatabaseMetaData md, String catalog, String schema, String tableName, boolean lowerCase)
            throws SQLException {
        this.catalog = catalog;
        this.schema = schema;
        this.tableName = tableName;
        this.lowerCase = lowerCase;
        this.primaryKey = buildPrimaryKey(md);
        this.columns = buildColumn(md);
        this.importedKeys = buildImportedKeys(md);
        this.exportedKeys = buildExportedKeys(md);
        this.indexs = buildUniqueKeysAndIndexs(md);
    }

    private PrimaryKeyInfo buildPrimaryKey(DatabaseMetaData md) throws SQLException {
        try (ResultSet rs = md.getPrimaryKeys(catalog, schema, tableName)) {
            PrimaryKeyInfo pkInfo = null;
            while (rs.next()) {
                String columnName = normalizeSQLName(rs.getString(PK_COLUMN_NAME));
                if (pkInfo == null) {
                    String name = rs.getString(PK_NAME);
                    if (name == null || name.isEmpty()) {
                        name = tableName + "_PK";
                    }
                    pkInfo = new PrimaryKeyInfo(name, schema, tableName);
                }
                pkInfo.add(columnName);
            }
            return pkInfo;
        }
    }

    private List<ColumnInfo> buildColumn(DatabaseMetaData md) throws SQLException {
        try (ResultSet rs = md.getColumns(catalog, schema, tableName.replace("/", "//"), null)) {
            List<ColumnInfo> list = new ArrayList<>();
            while (rs.next()) {
                String columnName = normalize(rs.getString("COLUMN_NAME"));
                String normalizedColumnName = normalizeSQLName(columnName);
                int columnType = rs.getInt("DATA_TYPE");
                String typeName = rs.getString("TYPE_NAME");
                Integer columnSize = (Integer) rs.getObject("COLUMN_SIZE");
                Integer columnDigits = (Integer) rs.getObject("DECIMAL_DIGITS");
                int columnIndex = rs.getInt("ORDINAL_POSITION");
                int nullable = rs.getInt("NULLABLE");
                String defaultValue = rs.getString("COLUMN_DEF");
                String describe = rs.getString("REMARKS");
                list.add(new ColumnInfo(columnName, normalizedColumnName, columnType, typeName, columnSize,
                        columnDigits, columnIndex, nullable, defaultValue, describe));
            }
            return list;
        }
    }

    private List<ForeignKeyInfo> buildImportedKeys(DatabaseMetaData md) throws SQLException {
        try (ResultSet rs = md.getImportedKeys(catalog, schema, tableName)) {
            Map<String, ForeignKeyInfo> map = new TreeMap<>();
            while (rs.next()) {
                String name = rs.getString(FK_NAME);
                String pkSchemaName = normalizeSQLName(rs.getString(FK_PK_SCHEMA_NAME));
                String pkTableName = normalizeSQLName(rs.getString(FK_PK_TABLE_NAME));
                String pkColumnName = normalizeSQLName(rs.getString(FK_PK_COLUMN_NAME));
                String localSchemaName = normalizeSQLName(rs.getString(FK_SCHEMA_NAME));
                String localTableName = normalizeSQLName(rs.getString(FK_TABLE_NAME));
                String localColumn = normalizeSQLName(rs.getString(FK_COLUMN_NAME));
                if (name == null || name.isEmpty()) {
                    name = tableName + "_" + pkTableName + "_FK";
                }

                ForeignKeyInfo fkInfo = map.get(name);
                if (fkInfo == null) {
                    fkInfo = new ForeignKeyInfo(name, localSchemaName, localTableName, pkSchemaName, pkTableName);
                    map.put(name, fkInfo);
                }
                fkInfo.add(localColumn, pkColumnName);
            }
            return new ArrayList<>(map.values());
        }
    }

    private List<ForeignKeyInfo> buildExportedKeys(DatabaseMetaData md) throws SQLException {
        try (ResultSet rs = md.getExportedKeys(catalog, schema, tableName)) {
            Map<String, ForeignKeyInfo> map = new TreeMap<>();
            while (rs.next()) {
                String name = rs.getString(FK_NAME);
                String pkSchemaName = normalizeSQLName(rs.getString(FK_PK_SCHEMA_NAME));
                String pkTableName = normalizeSQLName(rs.getString(FK_PK_TABLE_NAME));
                String pkColumnName = normalizeSQLName(rs.getString(FK_PK_COLUMN_NAME));
                String foreignSchemaName = normalizeSQLName(rs.getString(FK_SCHEMA_NAME));
                String foreignTableName = normalizeSQLName(rs.getString(FK_TABLE_NAME));
                String foreignColumn = normalizeSQLName(rs.getString(FK_COLUMN_NAME));
                if (name == null || name.isEmpty()) {
                    name = tableName + "_" + foreignTableName + "_IFK";
                }

                ForeignKeyInfo fkInfo = map.get(name);
                if (fkInfo == null) {
                    fkInfo = new ForeignKeyInfo(name, foreignSchemaName, pkSchemaName, foreignTableName,
                            pkTableName);
                    map.put(name, fkInfo);
                }
                fkInfo.add(foreignColumn, pkColumnName);
            }
            return new ArrayList<>(map.values());
        }
    }
    
    private List<IndexInfo> buildUniqueKeysAndIndexs(DatabaseMetaData md) throws SQLException {
        Map<String, IndexInfo> indexInfos = new TreeMap<>();
        try (ResultSet rs = md.getIndexInfo(catalog, schema, tableName, false, false)) {
            while (rs.next()) {
                String name = rs.getString("INDEX_NAME");
                boolean nonUnique = rs.getBoolean("NON_UNIQUE");
                String ascDesc = rs.getString("ASC_OR_DESC");
                String columnName = normalizeSQLName(rs.getString("COLUMN_NAME"));
                String indexSchemaName = normalizeSQLName(rs.getString("TABLE_SCHEM"));
                String indexTableName = normalizeSQLName(rs.getString("TABLE_NAME"));
                if (name == null || name.isEmpty()) {
                    continue;
                }

                IndexInfo idxInfo = indexInfos.get(name);
                if (idxInfo == null) {
                    idxInfo = new IndexInfo(name, indexSchemaName, indexTableName, !nonUnique);
                    indexInfos.put(name, idxInfo);
                }
                boolean desc = "D".equalsIgnoreCase(ascDesc);
                idxInfo.addColumn(new IndexColumn(columnName, desc));
            }
        }

        for (IndexInfo ixInfo : indexInfos.values()) {
            if (ixInfo.isSameColumn(primaryKey.getColumns())) {
                ixInfo.setPrimaryKeyIndex(true);
                continue;
            }
            for (ForeignKeyInfo fkInfo : importedKeys) {
                if (ixInfo.isSameColumn(fkInfo.getLocalColumns())) {
                    ixInfo.setForeignKeyIndex(true);
                    break;
                }
            }
        }
        return new ArrayList<>(indexInfos.values());
    }

    private String normalize(String str) {
        if (lowerCase && str != null) {
            return str.toLowerCase();
        } else {
            return str;
        }
    }

    public String getTableName() {
        return tableName;
    }

    /**
     * 返回主键
     * 
     * @return
     */
    public PrimaryKeyInfo getPrimaryKey() {
        return primaryKey;
    }

    /**
     * 返回列
     * @return
     */
    public List<ColumnInfo> getColumns() {
        return columns;
    }

    /**
     * 返回外键
     * 
     * @return
     */
    public List<ForeignKeyInfo> getImportedKeys() {
        return importedKeys;
    }

    /**
     * 返回引用给定表的主键列（表导入的外键）的外键列
     * 
     * @return
     */
    public List<ForeignKeyInfo> getExportedKeys() {
        return exportedKeys;
    }

    /**
     * 返回索引
     * 
     * @return
     */
    public List<IndexInfo> getIndexs() {
        return indexs;
    }

    private static String normalizeSQLName(String name) {
        if (name != null) {
            return name.replaceAll("\r", "").replaceAll("\n", " ");
        } else {
            return null;
        }
    }

    public static boolean isEquals(List<String> columns1, List<String> columns2) {
        Checks.verifyNotNull(columns1, "columns1");
        Checks.verifyNotNull(columns2, "columns2");
        if (columns1.size() != columns2.size()) {
            return false;
        }
        for (int i = 0; i < columns1.size(); i++) {
            String name1 = columns1.get(i);
            String name2 = columns2.get(i);
            if (!name1.equalsIgnoreCase(name2)) {
                return false;
            }
        }
        return true;
    }

}
